package com.tsfyun.common.base.help.excel;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.google.common.collect.Lists;
import com.tsfyun.common.base.help.excel.model.ExcelCellProp;
import com.tsfyun.common.base.util.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * ExcelUtil
 * 基于easyExcel的开源框架，poi版本3.17
 * BeanCopy ExcelException 属于自定义数据，属于可自定义依赖
 * 工具类尽可能还是需要减少对其他java的包的依赖
 * Created by z.mark on 2020/01/08.
 */
public class ExcelUtil {
    /**
     * 私有化构造方法
     */
    private ExcelUtil() {
    }

    /**
     * 读取 Excel(多个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T> rowModel) throws RuntimeException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        for (Sheet sheet : reader.getSheets()) {
            sheet.setClazz(rowModel);
            reader.read(sheet);
        }
        return getExtendsBeanList(excelListener.getDataList(), rowModel);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo) throws RuntimeException {
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo,
                                                             int headLineNum) throws RuntimeException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel));
        return getExtendsBeanList(excelListener.getDataList(), rowModel);
    }


    /**
     * 导出 Excel ：多个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                          String fileName, String sheetName, BaseRowModel object, ExcelTypeEnum excelTypeEnum) throws RuntimeException {
        ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response, excelTypeEnum), excelTypeEnum);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        return writer;
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) throws RuntimeException {
        //创建本地文件
        String filePath = fileName + excelTypeEnum.getValue();
        try {
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    public static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) throws RuntimeException {
        String fileName = excel.getOriginalFilename();
        if (fileName == null) {
            throw new RuntimeException("文件格式错误！");
        }
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream;

        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
//            , ExcelTypeEnum.XLSX
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            //do something
        }
        return null;
    }

    /**
     * 利用BeanCopy转换list
     */
    public static <T extends BaseRowModel> List<T> getExtendsBeanList(List<?> list, Class<T> typeClazz) {
        return MyBeanCopy.convert(list, typeClazz);
    }


    public static List<CellRangeAddress> getCombineCell(org.apache.poi.ss.usermodel.Sheet sheet) {
        List<CellRangeAddress> list = Lists.newArrayList();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @param listCombineCell 存放合并单元格的list
     * @param cell 需要判断的单元格
     * @param sheet sheet
     * @return
     */
    public static ExcelCellProp isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, org.apache.poi.ss.usermodel.Sheet sheet, FormulaEvaluator evaluator) {
        ExcelCellProp excelProp = new ExcelCellProp();
        excelProp.setIsCombineCell(false);
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for(CellRangeAddress ca : listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if(Objects.nonNull(cell) && cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC,Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    cellValue = getCellValue(fCell,evaluator);
                    excelProp.setIsCombineCell(Boolean.TRUE);
                    excelProp.setFirstColumn(firstC);
                    excelProp.setLastColumn(lastC);
                    excelProp.setFirstRow(firstR);
                    excelProp.setLastRow(lastR);
                    break;
                }
            }
            else
            {
                cellValue = "";
            }
        }
        excelProp.setCellValue(cellValue);
        return excelProp;
    }

    public static String getCellValue(Cell cell,FormulaEvaluator evaluator) {
        String value = "";
        if(Objects.nonNull(cell)) {
            String dataFormat = cell.getCellStyle().getDataFormatString();
            if(cell.getCellTypeEnum() == CellType.NUMERIC &&
                    ("yyyy/mm;@".equals(dataFormat) || "m/d/yy".equals(dataFormat)
                            || "yy/m/d".equals(dataFormat) || "mm/dd/yy".equals(dataFormat)
                            || "dd-mmm-yy".equals(dataFormat)|| "yyyy/m/d".equals(dataFormat)
                            || "yyyy/mm/dd".equalsIgnoreCase(dataFormat)
                            || "yyyy/m/d".equalsIgnoreCase(dataFormat))
                    && StringUtils.isNotEmpty(cell.getDateCellValue())){
                return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
            } else if(cell.getCellTypeEnum() == CellType.FORMULA) { //公式
                //cell = evaluator.evaluateInCell(cell);
                try {
                    value = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    if (e.getMessage().indexOf("from a STRING cell") != -1) {
                        try {
                            value = StringUtils.null2EmptyWithTrim(cell.getStringCellValue());
                        } catch (Exception e2) {
                            value = "";
                        }
                    } else if (e.getMessage().indexOf("from a ERROR formula cell") != -1) {
                        value = StringUtils.null2EmptyWithTrim(cell.getErrorCellValue());
                    } else {
                        //执行慢
                        cell = evaluator.evaluateInCell(cell);
                        return getNoFormulaCellValue(cell);
                    }
                } catch (Exception e) {

                }
                return value;
            }
            cell.setCellType(CellType.STRING);
            //去中间空格
            value = StringUtils.null2EmptyWithTrim(cell.getStringCellValue());
        }
        return value;
    }

    public static String getNoFormulaCellValue(Cell cell) {
        String value = "";
        String dataFormat = cell.getCellStyle().getDataFormatString();
        if(cell.getCellTypeEnum() == CellType.NUMERIC &&
                ("yyyy/mm;@".equals(dataFormat) || "m/d/yy".equals(dataFormat)
                        || "yy/m/d".equals(dataFormat) || "mm/dd/yy".equals(dataFormat)
                        || "dd-mmm-yy".equals(dataFormat)|| "yyyy/m/d".equals(dataFormat)
                        || "yyyy/mm/dd".equalsIgnoreCase(dataFormat)
                        || "yyyy/m/d".equalsIgnoreCase(dataFormat))
                && StringUtils.isNotEmpty(cell.getDateCellValue())){
            return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
        }
        //排除公式的情况
        cell.setCellType(CellType.STRING);
        //去中间空格
        value = StringUtils.null2EmptyWithTrim(cell.getStringCellValue());
        return value;
    }

}
